#    Copyright 2010, 2011, 2012 Tri-County Electric Cooperative, St. Matthews, South Carolina
#    This file is part of COOPIP.
#    COOPIP is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#    COOPIP is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#    You should have received a copy of the GNU General Public License
#    along with COOPIP.  If not, see <http://www.gnu.org/licenses/>.

#************************BEGIN Add Include Files to Path************************
import sys
NeededPaths=['/opt/coopip/djangoproject/include', '/opt/coopip/djangoproject']
for path in NeededPaths:
    if(not path in sys.path):
        sys.path.append(path)
#************************END   Add Include Files to Path************************
import COOPIP_Ajax, COOPIP_Misc, COOPIP_DynamicTemplate, COOPIP_Form, COOPIP_FormCISReport
import COOPIP_Authentication
import COOPIP_FormAMRReport
from djangoproject.models import *
import os, csv, shutil, datetime, time, reportlab, djangoproject.settings, random
from django.contrib.gis.db import models
from django.contrib.gis.geos import *
from django.http import HttpResponse
from django.contrib import admin
from django.template import Context, loader
import djangoproject.settings
import _mysql as mysql
from models import *
from django import forms
import AMR
import COOPIP_Misc
import CIS
import cPickle
import datetime
from django.db import transaction


@transaction.commit_on_success
def MainMenu(request):
    """MainMenu is the main selection screen for AMR Functions."""
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(
            request, ['COOPIP_RevenueProtectionTechnician', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/amrmainmenu.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    t = loader.get_template('/opt/coopip/djangoproject/templates/amrmainmenu.html')
    c = Context({'LoginStatus': LoginStatus})
    return HttpResponse(t.render(c))


def SubstationsOfflineTextOnly(request):
    """SubstationOfflineTextOnly is used by a script running on the AMR server to determine what substations are offline"""
    SubstationStatusTextRefDictionary={} #Key = substatus, value = subconnectstatustext
    Results=[]
    AMRSubstationStatuses=list(AMR.Substatetextxref.objects.all())
    for i in AMRSubstationStatuses:
        SubstationStatusTextRefDictionary[str(i.substatus)] = str(i.subconnectstatustext)

    Substations=list(AMR.Scemain.objects.all().order_by('subname'))
    for substation in Substations:
        try:
            SubstationState=SubstationStatusTextRefDictionary[str(substation.substate)]
        except:
            SubstationState='Offline'
        Results.append(str(substation.subname) + ', ' + str(SubstationState) + '\n')
    return HttpResponse(''.join(Results), content_type="text/plain")


@transaction.commit_on_success
def InvalidCycleNumbers(request):
    """Report that lists meter accounts with invalid metercyclenumbers"""
    GenericReportTitle='AMR Meters with Invalid Cycle Numbers'
    GenericMessage=''
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(
            request, ['COOPIP_RevenueProtectionTechnician', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/amrmainmenu.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Report=[]
    ValidCycles=[10, 11, 12, 13, 14, 15,  #per LWhalen
                 21, 22, 23, 24, 25, 
                 31, 32, 33, 34, 35, 
                 41, 42, 43, 44, 45, 
                 51, 52, 53, 54, 55, 
                 61, 62, 63, 64, 65, 
                 71, 72, 73, 74, 75, 
                 81, 82, 83, 84, 85, 
                 91, 92, 93, 94, 95, 
                 101, 102, 103, 104, 105, 
                 111, 112, 113, 114, 115, 
                 121, 122, 123, 124, 125, 
                 131, 132, 133, 134, 135, 
                 141, 142, 143, 144, 145, 
                 151, 152, 153, 154, 155, 
                 161, 162, 163, 164, 165, 
                 171, 172, 173, 174, 175, 
                 181, 182, 183, 184, 185, 
                 201, 203, 205, 207, 
                 99] #per SHeckle
    InvalidCycles=[]
    AllCycles=AMR.Meteraccts.objects.values_list('metercyclenumber', flat=True).distinct()
    for i in AllCycles:
        if not i in ValidCycles:
            InvalidCycles.append(i)
    InvalidAccounts=AMR.Meteraccts.objects.filter(metercyclenumber__in=InvalidCycles)
    Report.append('Total: '.rjust(20) + str(InvalidAccounts.count()))
    Report.append('Meters'.rjust(20) + 'Cycle Number'.rjust(20) + 'Premise ID'.rjust(20) + 'CIS Disconnect'.rjust(20))
    Rows=[]
    for i in InvalidAccounts:
        SerialNumbers=AMR.Serialnumber.objects.filter(serialnumber=i.serialnumber)
        if SerialNumbers.count():
            Location=SerialNumbers[0].premiseid
            CISLocation=Location.rjust(16,'0')
            CISMembers=CIS.Memberdetl.objects.filter(location=CISLocation)
            if CISMembers.count():
                DisconnectDate=CISMembers[0].discdate
            else:
                DisconnectDate='No Member at Location'
        else:
            CISLocation=DisconnectDate=''
        Row = (str(i.serialnumber).rjust(20) + str(i.metercyclenumber).rjust(20) + str(CISLocation).rjust(20) + DisconnectDate.rjust(20))
        Rows.append((DisconnectDate, Row))
    Rows.sort()
    for i in Rows:
        Disconnect, Data = i
        Report.append(Data)
    Preformatted='\n'.join(Report)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 
                 'GenericReportTitle' : GenericReportTitle, 'GenericMessage' : GenericMessage})
    return HttpResponse(t.render(c))


def UsageReportGetKWHUsageDict(PickledText):
    return cPickle.loads(str(PickledText))
    try:
        return cPickle.loads(str(PickledText))
    except cPickle.UnpicklingError:
        return {}

@transaction.commit_on_success
def HighestUsage(request):
    """Report that lists meter accounts with Highest Usage"""
    GenericReportTitle='AMR Meters with Highest Usage'
    GenericMessage=''
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(
            request, ['COOPIP_RevenueProtectionTechnician', 'COOPIP_RevenueProtectionAdmin'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/amrmainmenu.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Report=[]
    StartDate = (datetime.datetime.now() - datetime.timedelta(days=32)).date()
    EndDate = (datetime.datetime.now() - datetime.timedelta(days=2)).date()
    DateKeys=[]
    CurrentDate = StartDate
    MeterReads=MeterRead.objects.all()[1:5]
    while(CurrentDate <= EndDate):
        DateKeys.append(MeterReads[0].DateTimeToKey(CurrentDate))
        CurrentDate = CurrentDate + datetime.timedelta(days=1)
    Results = []  #List of lists with 2 elements, Meter Serial Number, Meter Usage
    MeterReads=list(MeterRead.objects.values_list('Meter', 'KWHUsage').order_by('id'))
    SerialNumberDict={}
    Meters=list(Meter.objects.all())
    for i in Meters:
        SerialNumberDict[str(i.id)] = i


    for meterRead in MeterReads:
        Total = 0
        UsageDict = UsageReportGetKWHUsageDict(meterRead[1])
        for key in DateKeys:
            try:
               Total = Total + UsageDict[key]
            except KeyError:
                pass
        meter = SerialNumberDict[str(meterRead[0])]
        Results.append([meter.SerialNumber, int(Total), meter.MeterModel.Name, meter.AMRSubstationName, meter.AMRCircuitName, meter.AMRPhaseCode])
    Results.sort(key=lambda element: -element[1])
    Report.append('Start Date:  ' + str(StartDate) + '  End Date:  ' + str(EndDate))
    Report.append('')
    Report.append('DCSI Number'.ljust(30) + 'KWH Usage'.ljust(25) + 'Meter Model'.ljust(20)  + 'Substation'.ljust(20) + 'Feeder'.ljust(20) + 'Phase'.ljust(20) )

    for result in Results:
        #if(result[0] in ['55268432']):
        #    Report.append(str('SOLAR' + str(result[0]).ljust(30)) + str(result[1]))
        #else:
        Report.append(str(result[0]).ljust(30) + str(result[1]).ljust(25) + str(result[2]).ljust(20) + str(result[3]).ljust(20) + str(result[4]).ljust(20) + str(result[5]).ljust(20))

  
    Preformatted='\n'.join(Report)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 
                 'GenericReportTitle' : GenericReportTitle, 'GenericMessage' : GenericMessage})
    return HttpResponse(t.render(c))







@transaction.commit_on_success
def RecentMeterReads(request):
    """Report that lists Recent Meter Reads with information to troubleshoot problems"""
    GenericReportTitle='Recent AMR Meters Reads With Quality Codes'
    GenericMessage=''
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(
            request, ['COOPIP_RevenueProtectionTechnician', 'COOPIP_RevenueProtectionAdmin', 'COOPIP_CISUser'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/amrmainmenu.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    form=COOPIP_Form.BuildDynamicForm(request, 'RecentMeterReads')
    StartDate, EndDate = COOPIP_FormAMRReport.ProcessRecentMeterReadsForm(form)
    form=COOPIP_FormAMRReport.PopulateRecentMeterReadsForm(form)
    #Perform Data Gathering Tasks
    Report=[]
    if StartDate and EndDate:
        Meters=list(Meter.objects.all())
        MeterDict={}
        for i in Meters:
            MeterDict[i.SerialNumber] = i
        #Meteracctsmits=list(AMR.Meteracctsmit.objects.filter(metertccurdatetime__gte=StartDate).filter(serialnumber=55178857).order_by('serialnumber', 'metertcqualitycode', '-metertccurdatetime'))
        Meteracctsmits=list(AMR.Meteracctsmit.objects.filter(metermitreaddt__gte=StartDate).filter(metermitreaddt__lte=EndDate).order_by('-metermitreaddt'))
        Meteracctstcs=list(AMR.Meteracctstc.objects.filter(metertccurdatetime__gte=StartDate).filter(metertccurdatetime__lte=EndDate).order_by('-metertccurdatetime'))
        Metermitresponselogdatas=list(AMR.Metermitresponselogdata.objects.filter(metermitreaddt__gte=StartDate).filter(metermitreaddt__lte=EndDate).order_by('-metermitreaddt'))
        Metertcresponselogs=list(AMR.Metertcresponselog.objects.filter(metertcrespcurdatetime__gte=StartDate).filter(metertcrespcurdatetime__lte=EndDate).order_by('-metertcrespcurdatetime'))	
        RaiseString = str(len(Meteracctsmits)) + ' ' + str(len(Meteracctstcs)) + ' ' + str(len(Metermitresponselogdatas)) + ' ' + str(len(Metertcresponselogs))
        #Display List:  SerialNumber, ReadDateTime
        DisplayList = []
        QualityCodeSummary = {}
        for i in Meteracctsmits:
            try:
                SubstationName=MeterDict[str(i.serialnumber)].AMRSubstationName
                FeederName=MeterDict[str(i.serialnumber)].AMRCircuitName
                Phase=MeterDict[str(i.serialnumber)].AMRPhaseCode
            except KeyError:
                SubstationName='UNKNOWN'
                FeederName='UNKNOWN'
                Phase=''
            DisplayList.append([i.serialnumber, i.metermitreaddt, COOPIP_Misc.CalculateQuality(i.metermitqualitycode), 'AMR', SubstationName, FeederName, Phase])
            if i.metermitqualitycode in QualityCodeSummary:
                QualityCodeSummary[i.metermitqualitycode] += 1
            else:
                QualityCodeSummary[i.metermitqualitycode] = 0
        for i in Meteracctstcs:
            try:
                SubstationName=MeterDict[str(i.serialnumber)].AMRSubstationName
                FeederName=MeterDict[str(i.serialnumber)].AMRCircuitName
                Phase=MeterDict[str(i.serialnumber)].AMRPhaseCode
            except KeyError:
                SubstationName='UNKNOWN'
                FeederName='UNKNOWN'
                Phase=''
            DisplayList.append([i.serialnumber, i.metertccurdatetime, COOPIP_Misc.CalculateQuality(i.metertcqualitycode), 'Meter', SubstationName, FeederName, Phase])
            if i.metertcqualitycode in QualityCodeSummary:
                QualityCodeSummary[i.metertcqualitycode] += 1
            else:
                QualityCodeSummary[i.metertcqualitycode] = 0
        for i in Metermitresponselogdatas:
            try:
                SubstationName=MeterDict[str(i.serialnumber)].AMRSubstationName
                FeederName=MeterDict[str(i.serialnumber)].AMRCircuitName
                Phase=MeterDict[str(i.serialnumber)].AMRPhaseCode
            except KeyError:
                SubstationName='UNKNOWN'
                FeederName='UNKNOWN'
                Phase=''
            DisplayList.append([i.serialnumber, i.metermitreaddt, COOPIP_Misc.CalculateQuality(i.metermitqualitycode), 'On Request', SubstationName, FeederName, Phase])
            if i.metermitqualitycode in QualityCodeSummary:
                QualityCodeSummary[i.metermitqualitycode] += 1
            else:
                QualityCodeSummary[i.metermitqualitycode] = 0
        for i in Metertcresponselogs:
            try:
                SubstationName=MeterDict[str(i.serialnumber)].AMRSubstationName
                FeederName=MeterDict[str(i.serialnumber)].AMRCircuitName
                Phase=MeterDict[str(i.serialnumber)].AMRPhaseCode
            except KeyError:
                SubstationName='UNKNOWN'
                FeederName='UNKNOWN'
                Phase=''
            DisplayList.append([i.serialnumber, i.metertcrespcurdatetime, COOPIP_Misc.CalculateQuality(i.metertcrespqualitycode), 'Metertcresponselogs', SubstationName, FeederName, Phase])
            if i.metertcrespqualitycode in QualityCodeSummary:
                QualityCodeSummary[i.metertcrespqualitycode] += 1
            else:
                QualityCodeSummary[i.metertcrespqualitycode] = 0
        Report.append('Quality Code'.ljust(25) + 'Quantity')
        for i in QualityCodeSummary:
            Report.append(COOPIP_Misc.CalculateQuality(i).ljust(30) + str(QualityCodeSummary[i]))
        Report.append('')
        Report.append('')
        DisplayList.sort(key=lambda element: [element[2], element[0]]) 
        Report.append('Meter DCSI Number'.ljust(20) + 'Read Date'.ljust(25) + 'QualityCode'.ljust(30) + 'Read Type'.ljust(25) + 'Substation'.ljust(20) + 'Feeder'.ljust(20) + 'Phase'.ljust(10))
        for i in DisplayList:
            Report.append(str(i[0]).ljust(20) + str(i[1]).ljust(25) + str(i[2]).ljust(30) + str(i[3]).ljust(25) + str(i[4]).ljust(20) + str(i[5]).ljust(20) + str(i[6]).ljust(10))
            #+ str(i.metertcqualitycode).ljust(20) + str(i.metertcreadtype).ljust(10) + str(i.metertctotalconsumpt).ljust(15))
    Preformatted='\n'.join(Report)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/amrrecentmeterreads.html')
    c = Context({'form': form, 'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 
                 'GenericReportTitle' : GenericReportTitle, 'GenericMessage' : GenericMessage})
    return HttpResponse(t.render(c))



def PrepaidConsecutivePendingReads(request):
    """Report that lists Recent Meter Reads with qualitycode 3 for meters in cycle 205; 
       These are prepaid accounts with two consecutive days of estimates caused by Pending commands"""
    GenericReportTitle='AMR Accounts with Recent \'Pending\' Reads'
    GenericMessage='These accounts have two days of readings marked \'Command Pending\ on Cycle 205 \n  Current starting point: ' + str(datetime.datetime.now().date())
    Permission, LoginStatus, Username = COOPIP_Authentication.COOPIPAuthenticate(
            request, ['COOPIP_RevenueProtectionTechnician', 'COOPIP_RevenueProtectionAdmin', 'COOPIP_CISUser'])
    if(not Permission):
        t = loader.get_template('/opt/coopip/djangoproject/templates/amrmainmenu.html')
        c = Context({'LoginStatus': LoginStatus, 'Permission': Permission})
        return HttpResponse(t.render(c))
    #Perform Data Gathering Tasks
    Accounts = AMR.Meteraccts.objects.filter(metercyclenumber=205)
    SerialNumbers = Accounts.values_list('serialnumber', flat=True)
    Report=[]
    Report.append('Account Number')
    Day1 = datetime.datetime.now().date()
    Day2 = datetime.datetime.now().date() - datetime.timedelta(days=1)
    Day3 = datetime.datetime.now().date() - datetime.timedelta(days=2)
    for i in SerialNumbers:
        ReadingsMIT = AMR.Meteracctsmit.objects.filter(serialnumber=i)
        ReadingsTC = AMR.Meteracctstc.objects.filter(serialnumber=i)
        if ReadingsMIT.count() > ReadingsTC.count():
            Readings = ReadingsMIT
            ReadingsDay1 = Readings.filter(metermitreaddt__gte=Day1).order_by('-metermitreaddt')
            ReadingsDay2 = Readings.filter(metermitreaddt__gte=Day2).filter(metermitreaddt__lte=Day1).order_by('-metermitreaddt')
            ReadingsDay3 = Readings.filter(metermitreaddt__gte=Day3).filter(metermitreaddt__lte=Day2).order_by('-metermitreaddt')
            if ReadingsDay1 and ReadingsDay2:
                Quality1 = ReadingsDay1[0].metercustomeracctnum
                Quality2 = ReadingsDay2[0].metercustomeracctnum
            elif ReadingsDay2 and ReadingsDay3:
                Quality1 = ReadingsDay2[0].metercustomeracctnum
                Quality2 = ReadingsDay3[0].metercustomeracctnum
            else:
                #readings not recent enough, discard for now
                #Report.append('Not enough readings ' + str(i))
                continue
            if Quality1 == Quality2 and Quality1 == 3:
                Report.append(str(ReadingDay1.metercustomeracctnum))
        elif ReadingsTC.count():
            Readings = ReadingsTC
            ReadingsDay1 = Readings.filter(metertcreaddt__gte=Day1).order_by('-metertcreaddt')
            ReadingsDay2 = Readings.filter(metertcreaddt__gte=Day2).filter(metertcreaddt__lte=Day1).order_by('-metertcreaddt')
            ReadingsDay3 = Readings.filter(metertcreaddt__gte=Day3).filter(metertcreaddt__lte=Day2).order_by('-metertcreaddt')
            if ReadingsDay1 and ReadingsDay2:
                Quality1 = ReadingsDay1[0].metercustomeracctnum
                Quality2 = ReadingsDay2[0].metercustomeracctnum
            elif ReadingsDay2 and ReadingsDay3:
                Quality1 = ReadingsDay2[0].metercustomeracctnum
                Quality2 = ReadingsDay3[0].metercustomeracctnum
            else:
                #readings not recent enough, discard for now
                #Report.append('Not enough readings ' + str(i))
                continue
            if Quality1 == Quality2 and Quality1 == 1:
                Report.append(str(ReadingDay1.metercustomeracctnum))
    Preformatted='\n'.join(Report)
    #End Data Gathering Tasks
    t = loader.get_template('/opt/coopip/djangoproject/templates/genericreporttemplate.html')
    c = Context({'LoginStatus' : LoginStatus, 'Preformatted': Preformatted, 
                 'GenericReportTitle' : GenericReportTitle, 'GenericMessage' : GenericMessage})
    return HttpResponse(t.render(c))
    


